﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using PS.DBUtility;
namespace PS.DAL
{
    /// <summary>
    /// 数据访问类:ExamRecord
    /// </summary>
    public partial class ExamRecord
    {
        public ExamRecord()
        { }
        #region  Method
        /// <summary>
        /// 是否存在该记录

        /// </summary>
        public bool Exists(int ExamRecordID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from ExamRecord");
            strSql.Append(" where ExamRecordID=@ExamRecordID");
            SqlParameter[] parameters = {
					new SqlParameter("@ExamRecordID", SqlDbType.Int,4)
};
            parameters[0].Value = ExamRecordID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据

        /// </summary>
        public int Add(PS.Model.ExamRecord model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ExamRecord(");
            strSql.Append("UserID,ExamSysParaID,StartDate,ExamMonth,FinishDate,InvigilateMan,ExamScore,ExamIDString,ExamValid,DispOrder,ValidMark,Spare1,Spare2,Spare3,Spare4,WorkType,WorkLevel)");
            strSql.Append(" values (");
            strSql.Append("@UserID,@ExamSysParaID,@StartDate,@ExamMonth,@FinishDate,@InvigilateMan,@ExamScore,@ExamIDString,@ExamValid,@DispOrder,@ValidMark,@Spare1,@Spare2,@Spare3,@Spare4,@WorkType,@WorkLevel)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4),
					new SqlParameter("@ExamSysParaID", SqlDbType.Int,4),
					new SqlParameter("@StartDate", SqlDbType.DateTime),
					new SqlParameter("@ExamMonth", SqlDbType.VarChar,20),
					new SqlParameter("@FinishDate", SqlDbType.DateTime),
					new SqlParameter("@InvigilateMan", SqlDbType.VarChar,200),
					new SqlParameter("@ExamScore", SqlDbType.Decimal,9),
					new SqlParameter("@ExamIDString", SqlDbType.VarChar,2000),
					new SqlParameter("@ExamValid", SqlDbType.Bit,1),
					new SqlParameter("@DispOrder", SqlDbType.Int,4),
					new SqlParameter("@ValidMark", SqlDbType.Int,4),
					new SqlParameter("@Spare1", SqlDbType.VarChar,200),
					new SqlParameter("@Spare2", SqlDbType.VarChar,200),
					new SqlParameter("@Spare3", SqlDbType.VarChar,200),
					new SqlParameter("@Spare4", SqlDbType.VarChar,200),
					new SqlParameter("@WorkType", SqlDbType.VarChar,100),
					new SqlParameter("@WorkLevel", SqlDbType.VarChar,100)};
            parameters[0].Value = model.UserID;
            parameters[1].Value = model.ExamSysParaID;
            parameters[2].Value = model.StartDate;
            parameters[3].Value = model.ExamMonth;
            parameters[4].Value = model.FinishDate;
            parameters[5].Value = model.InvigilateMan;
            parameters[6].Value = model.ExamScore;
            parameters[7].Value = model.ExamIDString;
            parameters[8].Value = model.ExamValid;
            parameters[9].Value = model.DispOrder;
            parameters[10].Value = model.ValidMark;
            parameters[11].Value = model.Spare1;
            parameters[12].Value = model.Spare2;
            parameters[13].Value = model.Spare3;
            parameters[14].Value = model.Spare4;
            parameters[15].Value = model.WorkType;
            parameters[16].Value = model.WorkLevel;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                model.ExamRecordID = Convert.ToInt32(obj);
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据

        /// </summary>
        public bool Update(PS.Model.ExamRecord model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ExamRecord set ");
            strSql.Append("UserID=@UserID,");
            strSql.Append("ExamSysParaID=@ExamSysParaID,");
            strSql.Append("StartDate=@StartDate,");
            strSql.Append("ExamMonth=@ExamMonth,");
            strSql.Append("FinishDate=@FinishDate,");
            strSql.Append("InvigilateMan=@InvigilateMan,");
            strSql.Append("ExamScore=@ExamScore,");
            strSql.Append("ExamIDString=@ExamIDString,");
            strSql.Append("ExamValid=@ExamValid,");
            strSql.Append("DispOrder=@DispOrder,");
            strSql.Append("ValidMark=@ValidMark,");
            strSql.Append("Spare1=@Spare1,");
            strSql.Append("Spare2=@Spare2,");
            strSql.Append("Spare3=@Spare3,");
            strSql.Append("Spare4=@Spare4,");
            strSql.Append("WorkType=@WorkType,");
            strSql.Append("WorkLevel=@WorkLevel");
            strSql.Append(" where ExamRecordID=@ExamRecordID");
            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4),
					new SqlParameter("@ExamSysParaID", SqlDbType.Int,4),
					new SqlParameter("@StartDate", SqlDbType.DateTime),
					new SqlParameter("@ExamMonth", SqlDbType.VarChar,20),
					new SqlParameter("@FinishDate", SqlDbType.DateTime),
					new SqlParameter("@InvigilateMan", SqlDbType.VarChar,200),
					new SqlParameter("@ExamScore", SqlDbType.Decimal,9),
					new SqlParameter("@ExamIDString", SqlDbType.VarChar,2000),
					new SqlParameter("@ExamValid", SqlDbType.Bit,1),
					new SqlParameter("@DispOrder", SqlDbType.Int,4),
					new SqlParameter("@ValidMark", SqlDbType.Int,4),
					new SqlParameter("@Spare1", SqlDbType.VarChar,200),
					new SqlParameter("@Spare2", SqlDbType.VarChar,200),
					new SqlParameter("@Spare3", SqlDbType.VarChar,200),
					new SqlParameter("@Spare4", SqlDbType.VarChar,200),
					new SqlParameter("@WorkType", SqlDbType.VarChar,100),
					new SqlParameter("@WorkLevel", SqlDbType.VarChar,100),
					new SqlParameter("@ExamRecordID", SqlDbType.Int,4)};
            parameters[0].Value = model.UserID;
            parameters[1].Value = model.ExamSysParaID;
            parameters[2].Value = model.StartDate;
            parameters[3].Value = model.ExamMonth;
            parameters[4].Value = model.FinishDate;
            parameters[5].Value = model.InvigilateMan;
            parameters[6].Value = model.ExamScore;
            parameters[7].Value = model.ExamIDString;
            parameters[8].Value = model.ExamValid;
            parameters[9].Value = model.DispOrder;
            parameters[10].Value = model.ValidMark;
            parameters[11].Value = model.Spare1;
            parameters[12].Value = model.Spare2;
            parameters[13].Value = model.Spare3;
            parameters[14].Value = model.Spare4;
            parameters[15].Value = model.WorkType;
            parameters[16].Value = model.WorkLevel;
            parameters[17].Value = model.ExamRecordID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据

        /// </summary>
        public bool Delete(int ExamRecordID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ExamRecord ");
            strSql.Append(" where ExamRecordID=@ExamRecordID");
            SqlParameter[] parameters = {
					new SqlParameter("@ExamRecordID", SqlDbType.Int,4)
};
            parameters[0].Value = ExamRecordID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条数据

        /// </summary>
        public bool DeleteList(string ExamRecordIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ExamRecord ");
            strSql.Append(" where ExamRecordID in (" + ExamRecordIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体

        /// </summary>
        public PS.Model.ExamRecord GetModel(int ExamRecordID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ExamRecordID,UserID,ExamSysParaID,StartDate,ExamMonth,FinishDate,InvigilateMan,ExamScore,ExamIDString,ExamValid,DispOrder,ValidMark,Spare1,Spare2,Spare3,Spare4,WorkType,WorkLevel from ExamRecord ");
            strSql.Append(" where ExamRecordID=@ExamRecordID");
            SqlParameter[] parameters = {
					new SqlParameter("@ExamRecordID", SqlDbType.Int,4)
};
            parameters[0].Value = ExamRecordID;

            PS.Model.ExamRecord model = new PS.Model.ExamRecord();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ExamRecordID"].ToString() != "")
                {
                    model.ExamRecordID = int.Parse(ds.Tables[0].Rows[0]["ExamRecordID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UserID"].ToString() != "")
                {
                    model.UserID = int.Parse(ds.Tables[0].Rows[0]["UserID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ExamSysParaID"].ToString() != "")
                {
                    model.ExamSysParaID = int.Parse(ds.Tables[0].Rows[0]["ExamSysParaID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["StartDate"].ToString() != "")
                {
                    model.StartDate = DateTime.Parse(ds.Tables[0].Rows[0]["StartDate"].ToString());
                }
                model.ExamMonth = ds.Tables[0].Rows[0]["ExamMonth"].ToString();
                if (ds.Tables[0].Rows[0]["FinishDate"].ToString() != "")
                {
                    model.FinishDate = DateTime.Parse(ds.Tables[0].Rows[0]["FinishDate"].ToString());
                }
                model.InvigilateMan = ds.Tables[0].Rows[0]["InvigilateMan"].ToString();
                if (ds.Tables[0].Rows[0]["ExamScore"].ToString() != "")
                {
                    model.ExamScore = decimal.Parse(ds.Tables[0].Rows[0]["ExamScore"].ToString());
                }
                model.ExamIDString = ds.Tables[0].Rows[0]["ExamIDString"].ToString();
                if (ds.Tables[0].Rows[0]["ExamValid"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["ExamValid"].ToString() == "1") || (ds.Tables[0].Rows[0]["ExamValid"].ToString().ToLower() == "true"))
                    {
                        model.ExamValid = true;
                    }
                    else
                    {
                        model.ExamValid = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["DispOrder"].ToString() != "")
                {
                    model.DispOrder = int.Parse(ds.Tables[0].Rows[0]["DispOrder"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ValidMark"].ToString() != "")
                {
                    model.ValidMark = int.Parse(ds.Tables[0].Rows[0]["ValidMark"].ToString());
                }
                model.Spare1 = ds.Tables[0].Rows[0]["Spare1"].ToString();
                model.Spare2 = ds.Tables[0].Rows[0]["Spare2"].ToString();
                model.Spare3 = ds.Tables[0].Rows[0]["Spare3"].ToString();
                model.Spare4 = ds.Tables[0].Rows[0]["Spare4"].ToString();
                model.WorkType = ds.Tables[0].Rows[0]["WorkType"].ToString();
                model.WorkLevel = ds.Tables[0].Rows[0]["WorkLevel"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ExamRecordID,UserID,ExamSysParaID,StartDate,ExamMonth,FinishDate,InvigilateMan,ExamScore,ExamIDString,ExamValid,DispOrder,ValidMark,Spare1,Spare2,Spare3,Spare4,WorkType,WorkLevel ");
            strSql.Append(" FROM ExamRecord ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据

        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ExamRecordID,UserID,ExamSysParaID,StartDate,ExamMonth,FinishDate,InvigilateMan,ExamScore,ExamIDString,ExamValid,DispOrder,ValidMark,Spare1,Spare2,Spare3,Spare4,WorkType,WorkLevel ");
            strSql.Append(" FROM ExamRecord ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取当前登录用户的当月考试次数
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataSet getCountExam(int userid)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" select count(*) ExamedNum,( select ExamTimes from ExamSysPara p  where p.ExamSysParaID=r.ExamSysParaID and p.ValidMark=1)ExamTimes, ");
            strSql.Append(" canExam= case when (( select ExamTimes from ExamSysPara p  where p.ExamSysParaID=r.ExamSysParaID and p.ValidMark=1 )-count(*))<0 ");
            strSql.Append(" then 0 else (( select ExamTimes from ExamSysPara p  where p.ExamSysParaID=r.ExamSysParaID and p.ValidMark=1 )-count(*)) end, ");
            strSql.Append("  istime =case when (getdate() > (select ExamStartDate from dbo.ExamSysPara where ExamSysParaID=r.ExamSysParaID)");
            strSql.Append(" and (getdate() <(select ExamFinishDate from dbo.ExamSysPara  where ExamSysParaID=r.ExamSysParaID)))");
            strSql.Append(" then 1 else 0 end from ExamRecord r   left join ExamSysPara esp on r.ExamSysParaid= esp.ExamSysParaid where r.UserID=@UserID and r.ValidMark=1 and   esp.CurrentPara=1  and  ");
            strSql.Append(" ISNULL(r.StartDate,'')!='' and ISNULL(r.FinishDate,'')!='' and r.ExamValid=1");
            strSql.Append(" and ((select convert(int,replace(convert(varchar(7),ExamStartDate,121),'-','')) ");
            strSql.Append(" from dbo.ExamSysPara where ExamSysParaID=r.ExamSysParaID) <= convert(int,replace(ExamMonth,'-','')))");
            strSql.Append(" and (convert(int,replace(ExamMonth,'-','')) >= (select convert(int,replace(convert(varchar(7),ExamStartDate,121),'-','')) ");
            strSql.Append(" from dbo.ExamSysPara where ExamSysParaID=r.ExamSysParaID))  group by r.ExamSysParaID ");


            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4)
                                        };
            parameters[0].Value = userid;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds == null || ds.Tables[0].Rows.Count < 1 || ds.Tables[0].Rows[0]["istime"].ToString() == "0")
            {
                StringBuilder strSqlelse = new StringBuilder();
                strSqlelse.Append(" select count(*) ExamedNum,( select top");
                strSqlelse.Append("  1 ExamTimes from ExamSysPara p  where ");
                strSqlelse.Append("  ValidMark=1 and CurrentPara=1  and (getdate() between ");
                strSqlelse.Append(" ExamStartDate and ExamFinishDate) order by CreateTime desc)");
                strSqlelse.Append("  ExamTimes,   canExam= case when (( select top 1 ExamTimes from ");
                strSqlelse.Append(" ExamSysPara p  where ValidMark=1 and CurrentPara=1  and (getdate() between ");
                strSqlelse.Append("  ExamStartDate and ExamFinishDate) )-count(*))<0 then 0 else    (( select top ");
                strSqlelse.Append(" 1 ExamTimes from ExamSysPara p  where ValidMark=1 and CurrentPara=1  and (getdate() between ");
                strSqlelse.Append(" ExamStartDate and ExamFinishDate) ");
                strSqlelse.Append("  )-count(*)) end,'' istime  from ExamRecord r  left join ExamSysPara esp on r.ExamSysParaid= esp.ExamSysParaid where r.UserID=@UserID  and esp.CurrentPara=1   ");
                strSqlelse.Append(" and r.ValidMark=1 and    ISNULL(r.StartDate,'')!='' and ISNULL(r.FinishDate,'')!='' ");
                strSqlelse.Append("  and ((select convert(int,replace(convert(varchar(7),ExamStartDate,121),'-',''))  ");
                strSqlelse.Append("  from dbo.ExamSysPara where ExamSysParaID=r.ExamSysParaID) <= convert(int,replace(ExamMonth,'-','')))");
                strSqlelse.Append("  and (convert(int,replace(ExamMonth,'-','')) >= (select");
                strSqlelse.Append("  convert(int,replace(convert(varchar(7),ExamStartDate,121),'-','')) from dbo.ExamSysPara");
                strSqlelse.Append(" where  r.ExamValid=1 and  (getdate() between ");
                strSqlelse.Append(" ExamStartDate and ExamFinishDate)))  and r.ExamValid=1  ");

                SqlParameter[] parameterselse = {
					new SqlParameter("@UserID", SqlDbType.Int,4)
                                        };
                parameterselse[0].Value = userid;

                ds = DbHelperSQL.Query(strSqlelse.ToString(), parameters);
            }
            return ds;
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataSet GetListByUser(int userid, long pageSize, long pageIndex, out long totalCount)
        {
            StringBuilder strSql = new StringBuilder();

            string totalSql = string.Format(" select count(*) from dbo.ExamRecord a where a.ValidMark=1 and a.ExamValid=1 and ISNULL(a.StartDate,'')!='' and ISNULL(a.FinishDate,'')!='' and a.UserID='{0}'", userid);

            totalCount = long.Parse(DbHelperSQL.ExecuteScalar(totalSql).ToString());

            strSql.Append("  select * from  (select top " + totalCount + " * from ( select top ");
            strSql.Append(pageSize * pageIndex <= totalCount ? pageSize : totalCount - pageSize * (pageIndex - 1));
            strSql.Append(" a.ExamMonth ExamMonths");
            strSql.Append(" ,convert(int,(convert(varchar(8),cast(row_number()over(partition by substring(a.ExamMonth,0,5)+substring(a.ExamMonth,6,7) ");
            strSql.Append(" order by a.ExamMonth,a.StartDate asc) ");
            strSql.Append(" as int)))) ExamNo,a.ExamScore ,esp.spare1,");
            strSql.Append(" (select substring(convert(varchar(20),round((sum(r.ExamScore)/");
            strSql.Append(" (select ExamTimes from dbo.ExamSysPara p where p.ExamSysParaID=r.ExamSysParaID)),2)),0, ");
            strSql.Append(" len(round((sum(r.ExamScore)/(select ExamTimes from dbo.ExamSysPara p ");
            strSql.Append(" where p.ExamSysParaID=r.ExamSysParaID)),2))-3) ExamScoreAvg  from ExamRecord r ");
            strSql.Append(" where r.ValidMark=a.ValidMark and r.UserID=a.UserID and  r.ExamMonth= a.ExamMonth  and");
            strSql.Append(" ISNULL(r.StartDate,'')!='' and ISNULL(r.FinishDate,'')!='' and r.ExamValid=1  and   r.ExamSysParaID=a.ExamSysParaID ");
            strSql.Append(" group by r.ExamSysParaID)ExamScoreAvg,");
            strSql.Append(" a.WorkType,a.WorkLevel,");
            strSql.Append(" convert(varchar(19),a.StartDate,121) ExamTime,a.ExamRecordID,a.UserID,a.ExamSysParaID");
            strSql.Append(" from dbo.ExamRecord a left join  ExamSysPara esp on esp.ExamSysParaID=a.ExamSysParaID where a.ExamRecordID not in (  select top ");
            strSql.Append((pageIndex - 1) * pageSize);
            strSql.Append(" a.ExamRecordID");
            strSql.Append(" from dbo.ExamRecord a where a.ValidMark=1 and a.UserID=@UserID and ISNULL(a.StartDate,'')!='' and ISNULL(a.FinishDate,'')!='' and a.ExamValid=1 ");
            strSql.Append(" order by a.ExamMonth desc) and a.ValidMark=1 and a.UserID=@UserID and ISNULL(a.StartDate,'')!='' and ISNULL(a.FinishDate,'')!='' and a.ExamValid=1");
            strSql.Append(" order by a.ExamMonth desc,ExamNo desc");
            strSql.Append(" )a order by ExamMonths desc");
            strSql.Append(" )a order by ExamMonths desc");

            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4)
                                        };
            parameters[0].Value = userid;

            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 根据当前登录人员的部门查看当前部门下人员考试记录
        /// </summary>
        /// <param name="username"></param>
        /// <param name="dep"></param>
        /// <param name="WorkType"></param>
        /// <param name="WorkLevel"></param>
        /// <param name="eq"></param>
        /// <param name="ExamScoreAvg"></param>
        /// <param name="Valid"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="orderStr"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataSet GetList(int userid, string username, string dep, string WorkType, string WorkLevel, string eq, string ExamScoreAvg, string year, string month, string orderStr, long pageSize, long pageIndex, out long totalCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select * from dbo.v_Exam where 1=1 ");
            strSql.Append(" and depname in ");
            strSql.Append(" (select DepName from empdep where depcode like ");
            strSql.Append(" ( select depcode from empdep d ");
            strSql.Append(" right join EmpPersonBase e on e.DepID=d.DepID where e.userid=").Append(userid);
            strSql.Append(" )+'%' )");

            if (!string.IsNullOrEmpty(username))
            {
                strSql.Append(" and PersonName like '%").Append(username).Append("%'");
            }
            if (!string.IsNullOrEmpty(dep))
            {
                strSql.Append(" and DepName like '%").Append(dep).Append("%'");
            }
            if (!string.IsNullOrEmpty(WorkType))
            {
                strSql.Append(" and WorkType = '").Append(WorkType).Append("'");
            }
            if (!string.IsNullOrEmpty(WorkLevel))
            {
                strSql.Append(" and WorkLevel = '").Append(WorkLevel).Append("'");
            }
            if (!string.IsNullOrEmpty(ExamScoreAvg))
            {
                if (eq == "大于等于")
                    eq = ">=";
                else if (eq == "等于")
                    eq = "=";
                else
                    eq = "<";
                strSql.Append(" and convert(numeric(6,2),ExamScoreAvg) ").Append(eq).Append(ExamScoreAvg);
            }
            if (!string.IsNullOrEmpty(year))
            {
                strSql.Append(" and (substring(ExamMonth,0,5)) like '%").Append(year).Append("%'");

            }
            if (!string.IsNullOrEmpty(month))
            {
                strSql.Append(" and (substring(ExamMonth,6,7)) like '%").Append(month.ToString().Length == 1 ? "0" +month.ToString() : month).Append("%'");

            }
            if (!string.IsNullOrEmpty(orderStr))
            {
                strSql.Append(" order by ExamMonth desc,DepName asc,ceiling(ExamScoreAvg) ").Append(orderStr);
            }
            else
                strSql.Append(" order by ExamMonth desc,DepName asc");

            string totalSql = " select count(*) from (" + strSql.ToString().Substring(0, strSql.ToString().LastIndexOf("order")) + ")a";
            totalCount = long.Parse(DbHelperSQL.ExecuteScalar(totalSql).ToString());

            string pageSql = PageSplitHelper.GetPageSql(strSql.ToString(), pageSize, pageIndex, totalCount);

            return DbHelperSQL.Query(pageSql);
        }

        /// <summary>
        /// 获取考试过的年份
        /// </summary>
        /// <returns></returns>
        public DataSet getYears(int userid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select distinct substring(ExamMonth,0,5) ExamMonth from v_Exam where 1=1");
            strSql.Append(" and depname in ");
            strSql.Append(" (select DepName from empdep where depcode like ");
            strSql.Append(" ( select depcode from empdep d ");
            strSql.Append(" right join EmpPersonBase e on e.DepID=d.DepID where e.userid=").Append(userid);
            strSql.Append(" )+'%' )");
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取选中用户当月考试信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="ExamMonth"></param>
        /// <returns></returns>
        public DataSet getExamByUserMonth(int userid, string ExamMonth, string ExamSysParaID, long pageSize, long pageIndex, out long totalCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select UserID,'第'+convert(varchar(8),cast(row_number() ");
            strSql.Append("over(partition by substring(ExamMonth,0,5) ");
            strSql.Append("+substring(ExamMonth,6,7)  order by ExamMonth)  ");
            strSql.Append("as int))+'次考试' ExamNo,ExamScore,convert(varchar(19),StartDate,121) ");
            strSql.Append("ExamTime from dbo.ExamRecord where ValidMark=1 and ISNULL(StartDate,'')!='' and ISNULL(FinishDate,'')!='' and ExamValid=1 and UserID='" + userid + "' and ExamMonth='" + ExamMonth + "' and ExamSysParaID='" + ExamSysParaID + "' ");

            string totalSql = " select count(*) from (" + strSql.ToString() + ")a";
            totalCount = long.Parse(DbHelperSQL.ExecuteScalar(totalSql).ToString());

            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        /// 根据当前登录人员的部门获取当前部门下人员考试记录（导出）
        /// </summary>
        /// <param name="username"></param>
        /// <param name="dep"></param>
        /// <param name="WorkType"></param>
        /// <param name="WorkLevel"></param>
        /// <param name="eq"></param>
        /// <param name="ExamScoreAvg"></param>
        /// <param name="Valid"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="orderStr"></param>
        /// <returns></returns>
        public DataSet GetList(int userid, string username, string dep, string WorkType, string WorkLevel, string eq, string ExamScoreAvg, string year, string month, string orderStr, bool ishas)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select DepName as '部门',PersonName as '员工姓名',Spare1 as '参数名称',ExamMonth as '考试月份',ExamScoreAvg as '平均成绩',WorkType as '工种',WorkLevel as '工种级别',UserID as '用户编号',ExamSysParaID  from dbo.v_Exam where 1=1 ");
            strSql.Append(" and depname in ");
            strSql.Append(" (select DepName from empdep where depcode like ");
            strSql.Append(" ( select depcode from empdep d ");
            strSql.Append(" right join EmpPersonBase e on e.DepID=d.DepID where e.userid=").Append(userid);
            strSql.Append(" )+'%' )");

            if (!string.IsNullOrEmpty(username))
            {
                strSql.Append(" and PersonName like '%").Append(username).Append("%'");
            }
            if (!string.IsNullOrEmpty(dep))
            {
                strSql.Append(" and DepName like '%").Append(dep).Append("%'");
            }
            if (!string.IsNullOrEmpty(WorkType))
            {
                strSql.Append(" and WorkType = '").Append(WorkType).Append("'");
            }
            if (!string.IsNullOrEmpty(WorkLevel))
            {
                strSql.Append(" and WorkLevel = '").Append(WorkLevel).Append("'");
            }
            if (!string.IsNullOrEmpty(ExamScoreAvg))
            {
                if (eq == "大于等于")
                    eq = ">=";
                else if (eq == "等于")
                    eq = "=";
                else
                    eq = "<";
                strSql.Append(" and convert(numeric(6,2),ExamScoreAvg) ").Append(eq).Append(ExamScoreAvg);
            }
            if (!string.IsNullOrEmpty(year))
            {
                strSql.Append(" and (substring(ExamMonth,0,5)) like '%").Append(year).Append("%'");

            }
            if (!string.IsNullOrEmpty(month))
            {
                strSql.Append(" and (substring(ExamMonth,6,7)) like '%").Append(month).Append("%'");

            }
            if (!string.IsNullOrEmpty(orderStr))
            {
                strSql.Append(" order by ExamMonth desc,DepName asc,ceiling(ExamScoreAvg) ").Append(orderStr);
            }
            else
                strSql.Append(" order by ExamMonth desc,DepName asc");


            DataSet ds = DbHelperSQL.Query(strSql.ToString());

            DataSet dsout = DbHelperSQL.Query(strSql.ToString());

            dsout.Tables[0].Columns.Remove("用户编号");
            dsout.Tables[0].Columns.Remove("ExamSysParaID");
            try
            {
                if (ishas)//如果包含每次考试记录 
                {
                    dsout.Tables[0].Rows.Clear();
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        DataRow drs = dsout.Tables[0].NewRow();
                        drs["部门"] = dr["部门"];
                        drs["员工姓名"] = dr["员工姓名"];
                        drs["参数名称"] = dr["参数名称"];
                        drs["考试月份"] = dr["考试月份"];
                        drs["平均成绩"] = dr["平均成绩"];
                        drs["工种"] = dr["工种"];
                        drs["工种级别"] = dr["工种级别"];
                        dsout.Tables[0].Rows.Add(drs);
                        //当前人员的某月记录
                        StringBuilder strSqlmonth = new StringBuilder();
                        strSqlmonth.Append("  select '' as '部门','' as '员工姓名','' as '参数名称','第'+convert(varchar(8),cast(row_number() ");
                        strSqlmonth.Append(" over(partition by substring(ExamMonth,0,5)  ");
                        strSqlmonth.Append(" +substring(ExamMonth,6,7)  order by ExamMonth)  ");
                        strSqlmonth.Append(" as int))+'次考试' as '考试月份',ExamScore as '平均成绩',convert(varchar(19),StartDate,121) as '工种' ");
                        strSqlmonth.Append(" ,UserID as '用户编号' from dbo.ExamRecord where  ValidMark=1 and ISNULL(StartDate,'')!='' and ISNULL(FinishDate,'')!='' and UserID=" + dr["用户编号"] + " and ExamMonth='" + dr["考试月份"] + "' and ExamSysParaID='" + dr["ExamSysParaID"] + "' ");
                        DataTable dt = DbHelperSQL.Query(strSqlmonth.ToString()).Tables[0];

                        foreach (DataRow dtres in dt.Rows)
                        {
                            DataRow dres = dsout.Tables[0].NewRow();

                            dres["部门"] = dtres["部门"];
                            dres["员工姓名"] = dtres["员工姓名"];
                            dres["参数名称"] = dtres["参数名称"];
                            dres["考试月份"] = dtres["考试月份"];
                            dres["平均成绩"] = dtres["平均成绩"];
                            dres["工种"] = dtres["工种"];
                            dsout.Tables[0].Rows.Add(dres);
                        }
                    }
                    return dsout;
                }
                else
                    return dsout;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "ExamRecord";
            parameters[1].Value = "ExamRecordID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  Method
    }
}

